package com.tbit.uqbike.webmanager.util;

import com.tbit.uqbike.enums.SystemCodeEnum;
import com.tbit.uqbike.manager.exception.BaseException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Number;
import jxl.write.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;

/**
 * 导出excel
 *
 * @author Leon
 * 2013-1-17 下午05:12:27
 */
@Slf4j
public class ExportExcelUtils {

    /**
     * ***********设置单元格字体**************
     */
    private static final WritableFont NORMAL_FONT = new WritableFont(WritableFont.ARIAL, 10);
    private static final WritableFont BOLD_FONT = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    // 用于标题居中
    private static WritableCellFormat wcf_center;
    // 用于正文居左
    private static WritableCellFormat wcf_left;

    static {
        try {
            // 用于标题居中
            wcf_center = new WritableCellFormat(BOLD_FONT);
            wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
            wcf_center.setWrap(false); // 文字是否换行

            // 用于正文居左
            wcf_left = new WritableCellFormat(NORMAL_FONT);
            wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_left.setAlignment(Alignment.CENTRE); // 文字水平对齐
            wcf_left.setWrap(false); // 文字是否换行
        } catch (WriteException e) {
            log.error(e.getMessage(), e);
        }
    }

    private ExportExcelUtils() {


    }

    /**
     * 导出Excel
     *
     * @param response
     * @param fileName
     * @param values
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<String[]> values) {


        /**头设置*/
        response.setContentType("application/x-msdownload");
        try {
            /**gb2312才能兼容所有浏览器*/
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
        } catch (UnsupportedEncodingException e) {
            log.error(e.getMessage(), e);
        }
        response.setCharacterEncoding("UTF-8");

        /**格式*/
        WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false);
        WritableCellFormat cwcfF = new WritableCellFormat(wf);
        try {
            cwcfF.setAlignment(Alignment.CENTRE);
        } catch (WriteException e1) {
            log.error(e1.getMessage(), e1);
        }
        /**生成内容*/
        WritableWorkbook wwb;
        Label label;
        try {
            wwb = Workbook.createWorkbook(response.getOutputStream());
            WritableSheet ws = wwb.createSheet("excel", 0);
            if (ws != null) {
                for (int i = 0; i < values.size(); i++) {
                    String[] line = values.get(i);
                    for (int j = 0; j < line.length; j++) {
                        label = new Label(j, i, line[j], cwcfF);
                        ws.addCell(label);
                        ws.setColumnView(j, 25);
                    }
                }
                wwb.write();
            }
            wwb.close();
        } catch (Exception e) {
            log.error(e.toString());
        }
    }

    private static WritableWorkbook createWritableWorkbook(OutputStream os, ColInfo[] colInfos, String sheetName) {
        /** **********创建工作簿************ */
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(os);

            /** **********创建工作表************ */
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            /** **********设置纵横打印（默认为纵打）、打印纸***************** */
            jxl.SheetSettings sheetset = sheet.getSettings();
            sheetset.setProtected(false);

            /** ***************以下是EXCEL第一行列标题********************* */
            for (int i = 0; i < colInfos.length; i++) {
                ColInfo col = colInfos[i];
                if (col.w <= 0) {
                    sheet.setColumnView(i, col.name.length() * 2);
                } else {
                    sheet.setColumnView(i, col.w);
                }
                sheet.addCell(new Label(i, 0, col.name, wcf_center));
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return workbook;
    }

    /**
     * 导出统计数据
     *
     * @param titles
     * @param listContent
     * @return
     */
    public static byte[] exportExcelDataV2(ColInfo[] titles, List<RowData> listContent) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        // 以下开始输出到EXCEL
        try {
            String sheetName = "Sheet1";
            WritableWorkbook workbook = createWritableWorkbook(os, titles, sheetName);
            WritableSheet sheet = workbook.getSheet(sheetName);
            /** ***************以下是EXCEL正文数据********************* */
            int i = 1;
            for (RowData item : listContent) {
                writeRow(sheet, titles, item, i);
                i++;
            }
            workbook.write();
            workbook.close();
            return os.toByteArray();
        } catch (Exception e) {
            log.error("系统提示：Excel文件导出失败，原因：{}", e.getMessage(), e);
            return null;
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    private static void writeRow(WritableSheet sheet, ColInfo[] titles, RowData row, Integer i) throws WriteException {
        if (null != row) {
            for (int j = 0; j < titles.length; j++) {
                ColInfo col = titles[j];
                writeSheet(row, i, j, col, sheet);
            }
        }
    }

    private static void writeSheet(RowData item, Integer i, Integer j, ColInfo col, WritableSheet sheet) throws WriteException {
        WritableCell cell = null;
        if (Objects.equals(col.cellType, CellType.NUMERIC)) {
            Double num = item.getDouble(j);
            if (null != num) {
                cell = new Number(j, i, num, wcf_left);
            }
        } else {
            String str = item.getString(j);
            if (null != str) {
                cell = new Label(j, i, str, wcf_left);
            }
        }
        if (null != cell) {
            sheet.addCell(cell);
        }
    }

    public static List<List<String>> readExcelData(byte[] fileData) {
        List<List<String>> data = new LinkedList<>();
        ByteArrayInputStream stream = null;
        try {
            stream = new ByteArrayInputStream(fileData);
            org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(stream);
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum();
            for (int i = 1; i <= count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(0) == null) {
                    throw new BaseException(SystemCodeEnum.EXCLE_MODEL_ERROR);
                }
                if (row.getCell(1) == null) {
                    throw new BaseException(SystemCodeEnum.EXCLE_MODEL_ERROR);
                }
                List<String> temp = new LinkedList<>();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    temp.add(cell.getStringCellValue());
                }
                data.add(temp);
            }
        } catch (IllegalStateException ise) {
            throw new BaseException(SystemCodeEnum.EXCLE_ONLY_ACCEPT_STRING);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            if (null != stream) {
                try {
                    stream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
        return data;
    }

    public static List<List<String>> readExcelDataFirst(byte[] fileData, Integer type) {
        List<List<String>> data = new LinkedList<>();
        ByteArrayInputStream stream = null;
        try {
            stream = new ByteArrayInputStream(fileData);
            org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(stream);
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum();
            /**第一行为标题行*/
            Row firstRow = sheet.getRow(0);
            int cells = firstRow.getPhysicalNumberOfCells();
            for (int i = 1; i <= count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(0) == null) {
                    throw new BaseException(SystemCodeEnum.EXCLE_MODEL_ERROR);
                }
                if (row.getCell(1) == null && type == 1) {
                    throw new BaseException(SystemCodeEnum.EXCLE_MODEL_ERROR);
                }
                List<String> temp = new LinkedList<>();
                for (int m = 0; m < cells; m++) {
                    Cell cell = row.getCell(m);
                    if (cell != null) {
                        DataFormatter formatter = new DataFormatter();
                        String cellValue = formatter.formatCellValue(cell).trim();
                        temp.add(cellValue);
                    } else {
                        temp.add("");
                    }
                }
                data.add(temp);
            }
        } catch (IllegalStateException ise) {
            throw new BaseException(SystemCodeEnum.EXCLE_ONLY_ACCEPT_STRING);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            if (null != stream) {
                try {
                    stream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
        return data;
    }

    public static class ColInfo {
        public String name;
        // 默认为字符串，支持数字
        // CELL_TYPE_STRING CELL_TYPE_NUMERIC
        public CellType cellType;
        public Integer w = -1;

        public ColInfo(String name, CellType cellType, Integer w) {
            this.name = name;
            this.cellType = cellType;
            this.w = w;
        }
    }

    // 行对象
    public static abstract class RowData {
        public abstract String getString(Integer i);

        public abstract Double getDouble(Integer i);
    }

}
